package YNU.dao.impl;

import YNU.dao.StudentDao;
import YNU.po.Student;
import YNU.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.Scanner;

public class StudentDaoImpl implements StudentDao {
    private Scanner input = new Scanner(System.in);

    private Connection con = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;

    @Override
    public List<Student> listStudent(String name, String password) {
        List<Student> list = new ArrayList<>();
        StringBuffer sql = new StringBuffer("select * from Student where 1=1 ");
        if (name != null && !name.equals("")) {
            sql.append(" and name like '%" + name + "%' ");
        }
        if (password != null && !password.equals("")) {
            sql.append(" and password like '%" + password + "%' ");
        }
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setSid(rs.getInt("sid"));
                student.setPassword(rs.getString("password"));
                student.setName(rs.getString("name"));
                student.setMath(rs.getInt("Math"));
                student.setEnglish(rs.getInt("English"));
                //               student.setBusinessExplain(rs.getString("businessExplain"));
//                student.setStarPrice(rs.getDouble("starPrice"));
//                student.setDeliveryPrice(rs.getDouble("deliveryPrice"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, pst, con);
        }
        return list;
    }


    public int saveStudent(String name) {
        int sid = 0;
        String sql = "insert into student(name,password) values(?,'123')";//改成111
        try {
            con = DBUtil.getConnection();
            //设置返回自增长列值
            pst = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            pst.setString(1, name);
            pst.executeUpdate();
            //获取自增长列值（一行一列）
            rs = pst.getGeneratedKeys();
            if (rs.next()) {
                sid = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, pst, con);
        }
        return sid;
    }

    public int removeStudent(int sid) {
        int result = 0;
       // String delFootSql = "delete from student where sid=?";
        String delStudentSql = "delete from student where sid=?";
        try {
            con = DBUtil.getConnection();
            //开启一个事务
            con.setAutoCommit(false);//
			/*下面的代码利用 setAutoCommit 方法将 Connection 对象设为手动提交方式。
Connection conn = ……  //省略部分代码
conn.setAutoCommit(false);
*/

//            pst = con.prepareStatement(delFootSql);
//            pst.setInt(1, sid);
//            pst.executeUpdate();

            pst = con.prepareStatement(delStudentSql);
            pst.setInt(1, sid);
            result = pst.executeUpdate();

            con.commit();
        } catch (SQLException e) {
            result = 0;
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DBUtil.close(null, pst, con);
        }
        return result;
    }


    public int updateStudent(Student student) {
        int result = 0;
        String sql = "update student set name=?,password=?,Math=?,English=? where sid=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
          //  pst.setInt(1, student.getSid());
            pst.setString(1, student.getName());//setString是啥
            // pst.setString(2, student.getBusinessAddress());
            pst.setString(2, student.getPassword());
            pst.setInt(3, student.getMath());
            pst.setInt(4, student.getEnglish());
            pst.setInt(5, student.getSid());
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, pst, con);
        }
        return result;
    }

    public Student getStudentByIdByPass(Integer sid, String password) {
        Student student = null;
        String sql = "select * from Student where sid=? and password=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            pst.setInt(1, sid);
            pst.setString(2, password);
            rs = pst.executeQuery();
            while (rs.next()) {
                student = new Student();
                student.setSid(rs.getInt("sid"));

                student.setPassword(rs.getString("password"));

                student.setName(rs.getString("name"));
                student.setMath(rs.getInt("Math"));
                student.setEnglish(rs.getInt("English"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, pst, con);
        }
        return student;
    }

    public Student getStudentById(Integer sid) {
        Student student = null;
        String sql = "select * from student where sid=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            pst.setInt(1, sid);
            rs = pst.executeQuery();
            while (rs.next()) {
                student = new Student();
                student.setSid(rs.getInt("sid"));

                student.setPassword(rs.getString("password"));

                student.setName(rs.getString("name"));
                student.setMath(rs.getInt("Math"));
                student.setEnglish(rs.getInt("English"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, pst, con);
        }
        return student;
    }
    public int updateStudentByPassword(Integer sid,String password){
        int result = 0;
        String sql = "update Student set password=? where sid=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, password);
            pst.setInt(2, sid);
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, pst, con);
        }
        return result;
    }

}
